package name.wilbur.course.db.bigone.dao;
/**
 * @author wilbur
 */
import javax.sql.DataSource;

import java.util.ArrayList;
import java.util.Arrays;
import java.util.List;
import java.lang.reflect.Array;
import java.lang.reflect.Field;
import java.lang.reflect.InvocationTargetException;
import java.lang.reflect.Method;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;


import name.wilbur.course.db.bigone.model.BaseDBModel;
import name.wilbur.course.db.bigone.model.DBTypeMap;

import org.apache.commons.lang3.StringUtils;
import org.apache.log4j.Logger;


public abstract class BaseDAO implements IDAO {
	protected Logger log;
	
	protected Class<BaseDBModel> entity;
	protected String primaryKey;
	protected String table;
	protected Field[] fields;
	protected String LAST_PK_SQL;
	protected String INSERT_SQL;
	protected String SELECT_SQL;
	protected String UPDATE_SQL;
	protected String DELETE_SQL;
	
	protected DataSource ds;
	protected Statement stmt;
	protected PreparedStatement pstmt;
	protected Connection conn;
	protected ResultSet rs;
	
	/**
	 * @param table name of the table
	 * @param fields contains table attributes separated by space,
	 * 				and the first attribute is primary key.
	 */
	public BaseDAO(Class entity) {
		this.entity = entity;
		this.primaryKey = entity.getSimpleName() + "ID";
		this.table = entity.getSimpleName() + "s";
		this.fields = entity.getDeclaredFields();
		GuessSql();
	}
	
	private void GuessSql() {
		int fields_num = fields.length;
		String field_names[] = new String[fields_num];
		for (int i=0;i<fields_num;i++) {
			field_names[i] = StringUtils.capitalize(fields[i].getName());
		}
		
		String insert_fields = StringUtils.join(field_names, ", ");
		String return_fields = primaryKey + ", " + insert_fields;
		String where_clause = " WHERE " + primaryKey + " = ? ";
		
		this.LAST_PK_SQL = "SELECT LAST_INSERT_ID()";
		this.SELECT_SQL = "SELECT " + return_fields + " FROM " + this.table;		
		this.DELETE_SQL = "DELETE FROM " + this.table + where_clause;
		this.INSERT_SQL = "INSERT INTO " + this.table + " ( " + insert_fields + " )" + 
							" VALUES ( " + StringUtils.repeat("?", ", ", fields_num) + " )";
		this.UPDATE_SQL = "UPDATE " + this.table + " SET " + 
							StringUtils.join(field_names, " = ?, ") + 
							" = ? " + where_clause;
	}

	/* abstract methods must be implemented by sub-classes. */
	public BaseDBModel findByPK(int pk) {
		BaseDBModel dbobj = null;
		try {
			log().debug("Finding " + table + " by " + primaryKey + ": " + pk);
			conn = ds.getConnection();
			pstmt = conn.prepareStatement(SELECT_SQL + " WHERE " + primaryKey + " = ? ");
			/* update pstmt */
			pstmt.setInt(1, pk);
			log().debug(pstmt);
			rs = pstmt.executeQuery();
			if (rs.next())
				dbobj = retrieveDBObject(rs);
		} catch (NumberFormatException e) {
			e.printStackTrace();
		} catch (SQLException e) {
			e.printStackTrace();
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			closeConnection();
		}
		return dbobj;
	}
	
	public List<BaseDBModel> findAll() {
		return find("SELECT * FROM " + table);
	}
	
	public List<BaseDBModel> find(String sql) {
		Object objs = null;
		List<BaseDBModel> list = new ArrayList<BaseDBModel>();
		log().debug(sql);
		try {
			conn = ds.getConnection();
			stmt = conn.createStatement();
			rs = stmt.executeQuery(sql);
			log().debug(sql);
			for (int i=0; rs.next(); i++) {
				BaseDBModel obj = retrieveDBObject(rs);
				if (obj != null)
				list.add(obj);
			}
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			closeConnection();
		}
		return list;
	}
	
	public BaseDBModel[] findWhere(String where_clasue) {
		String sql = SELECT_SQL + " WHERE " + where_clasue;
		String cntSql = "SELECT COUNT(*) FROM " + table + " WHERE " + where_clasue;
		Object objs = null;
		
		log().debug(sql);
		try {
			conn = ds.getConnection();
			pstmt = conn.prepareStatement(cntSql);
			rs = pstmt.executeQuery();
			log().debug(pstmt);
			if (rs.next()) {
				int cnt = rs.getInt(1);
				if (cnt <= 0) 
					return null;
				objs = Array.newInstance(entity, cnt);
				log().debug("COUNT: "+ cnt);
				pstmt = conn.prepareStatement(sql);
				rs = pstmt.executeQuery();
				for (int i=0; rs.next(); i++) {
					Array.set(objs, i, retrieveDBObject(rs));
				}
			}
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			closeConnection();
		}
		return (BaseDBModel[])objs;
	}
	
	public boolean create(BaseDBModel dbentity) {
		BaseDBModel dbobj = null;
		try {
			log().debug("Create a new record in " + table);
			conn = ds.getConnection();
			
			dbobj = entity.cast(dbentity);
			if (dbobj == null)
				return false;
			pstmt = conn.prepareStatement(INSERT_SQL);
			/* update pstmt */
			updatePstatement(dbobj);
			log().debug(pstmt);
			pstmt.executeUpdate();
			/* obtain primary key */
			pstmt = conn.prepareStatement(LAST_PK_SQL);
			rs = pstmt.executeQuery();
			if (rs.next())
				dbobj.setPk(rs.getInt(1));
			log().debug("Insert into " + table + ": " + dbobj.getPk());
		
			return true;
		} catch (Exception e) {
			/* todo: a variety of exceptions to handle here */
			e.printStackTrace();
			return false;
		} finally {
			closeConnection();
		}
	}
	
	public boolean update(BaseDBModel dbentity) {
		BaseDBModel dbobj = null;
		int pk_where = 0;    /* the pk index in pstmt where clause */
		try {
			conn = ds.getConnection();
			dbobj = entity.cast(dbentity);
			log().debug("Updating "+ table + " by " + primaryKey + ": " + dbobj.getPk());
			pstmt = conn.prepareStatement(UPDATE_SQL);
			/* update pstmt */
			pk_where = updatePstatement(dbobj);
			pstmt.setInt(pk_where, dbobj.getPk());
			log().debug(pstmt);
			pstmt.executeUpdate();
			return true;
		} catch (Exception e) {
			e.printStackTrace();
			return false;
		} finally {
			closeConnection();
		}
	}
	
	public boolean delete(int pk) {
		try {
			log().debug("Deleting " + table + " by " + primaryKey + ": " + pk);
			conn = ds.getConnection();
			pstmt = conn.prepareStatement(DELETE_SQL);
			pstmt.setInt(1, pk);
			log().debug(pstmt);
			pstmt.executeUpdate();
			return true;
		} catch (SQLException e) {
			e.printStackTrace();
			return false;
		} finally {
			closeConnection();
		}
	}
	
	public void create(BaseDBModel[] dbentity) {
		for (int i=0;i<dbentity.length;i++)
			create(dbentity[i]);
	}
	public void update(BaseDBModel[] dbentity) {
		for (int i=0;i<dbentity.length;i++)
			update(dbentity[i]);
	}
	public void delete(BaseDBModel[] dbentity) {
		for (int i=0;i<dbentity.length;i++)
			delete(dbentity[i].getPk());
	}
	
	public void query(String sql) {
		try {
			queryNotClose(sql);
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			closeConnection();
		}
	}

	public void queryNotClose(String sql) throws SQLException{
		try {
			conn = ds.getConnection();
			pstmt = conn.prepareStatement(sql);
			log().debug(pstmt);
			rs = pstmt.executeQuery();
		} catch (SQLException e) {
			e.printStackTrace();
			closeConnection();
		}
	}
	
	public void closeConnection() {
        try { 
        	if (rs != null) rs.close(); 
        	rs = null; 
        } catch(Exception e) {
        		e.printStackTrace();
        } try { 
        	if (pstmt != null) pstmt.close();
        	pstmt = null;
        } catch(Exception e) { 
        	e.printStackTrace();
        } try { 
        	if (conn != null) conn.close(); 
        	conn = null; 
        } catch(Exception e) { 
        	e.printStackTrace(); 
        }
	}
	
	public void clearDB() {
		String sql = "TRUNCATE TABLE " + table;
		try {
			conn = ds.getConnection();
			stmt = conn.createStatement();
			stmt.executeUpdate(sql);
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			closeConnection();
		}
	}
	
	@Override
	public ResultSet getResultSet() {
		return this.rs;
	}
	
	public void setDataSource(DataSource ds) {
		this.ds = ds;
	}
	
	public DataSource getDataSource() {
		return this.ds;
	}
	public Logger log() {
		if (log == null) {
			log = Logger.getLogger(getClass());
		}
		return log;
	}
	
	@Override
	public String toString() {
		return getClass().getName() + "\n [table=" + table + ", fields="
				+ Arrays.toString(fields) + ", INSERT_SQL=" + INSERT_SQL
				+ ", SELECT_SQL=" + SELECT_SQL + ", UPDATE_SQL=" + UPDATE_SQL
				+ ", DELETE_SQL=" + DELETE_SQL + "]";
	}
	
	protected String getGetter(String field_name) {
		return("get" + StringUtils.capitalize(field_name));
	}
	
	protected String getSetter(String field_name) {
		return ("set" + StringUtils.capitalize(field_name));
	}
	
	/**
	 * @param obj Object of the subclass of DBEntityBase 
	 * @return the next index for pstmt to set.
	 * @throws SecurityException
	 * @throws NoSuchMethodException
	 * @throws IllegalArgumentException
	 * @throws SQLException
	 * @throws IllegalAccessException
	 * @throws InvocationTargetException
	 */
	protected int updatePstatement(Object obj) throws 
			SecurityException, NoSuchMethodException, IllegalArgumentException, 
			SQLException, IllegalAccessException, InvocationTargetException {
		if (obj == null)
			return 0;
		Method mtd = null;
		int index = 0;
		while (index < fields.length) {
			mtd = entity.getMethod(
					getGetter(fields[index].getName()), 
					(Class[])null
			);
			pstmt.setObject(
					++index, 
					mtd.invoke(obj, (Object[])null)
			);
		}
		return(index+1);
	}
	
	protected BaseDBModel retrieveDBObject(ResultSet rs) throws 
			SecurityException, NoSuchMethodException, IllegalArgumentException, 
			IllegalAccessException, InvocationTargetException, SQLException, 
			InstantiationException {
		
		BaseDBModel dbobj = entity.newInstance();
		Method mtd= null;
		dbobj.setPk(rs.getInt(1));
		for (int i=0;i<fields.length;i++) {
			mtd = entity.getMethod(
					getSetter(fields[i].getName()), 
					new Class[] { fields[i].getType() }
			);
			Object obj = rs.getObject(i+2, DBTypeMap.getTypeMap());
			mtd.invoke(
					dbobj, 
					new Object[]{ obj }    /* skip the first column(pk) */
			);
		}
		return dbobj;
	}
}
